# -*- coding: utf-8 -*-
"""
-------------------------------------------------
   File Name：    excel_utils
   Author：       yubb
   date：         2021/7/1 10:42:50
   Description：  
  
-------------------------------------------------
"""
from io import BytesIO
from typing import List, Dict, Tuple

import openpyxl
from openpyxl import Workbook
from openpyxl.reader.excel import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.styles import Alignment, Font
from openpyxl.utils import get_column_letter
from typing_extensions import TypedDict


class HeadDict(TypedDict):
    code: str
    name: str


class ExcelOperate(object):

    @classmethod
    def parse_import(cls, headers: List[HeadDict], file_data) -> Tuple[List[str], List[Dict]]:
        """返回解析出来的表头和对应表头组成的数据"""
        wb: Workbook = load_workbook(BytesIO(file_data))
        sheet: Worksheet = wb.active

        # 解析出所有数据格式, 返回
        # 检查表头
        en_names = [i['code'] for i in headers]
        cn_names = [i['name'] for i in headers]

        parse_result = []
        parse_headers = []
        for idx, row in enumerate(sheet.rows):
            append_item = {}
            for code_idx, cell in enumerate(row):
                val = cell.value
                if idx == 0:
                    parse_headers.append(val)
                else:
                    if code_idx < len(en_names):
                        append_item[en_names[code_idx]] = val
                    else:
                        break
            if idx > 0:
                parse_result.append(append_item)

        return parse_headers, parse_result


    @classmethod
    def get_excel_data(cls, headers: List[HeadDict], body_data: List[Dict[str, object]],
                       sheet_title: str = None, key_map=None) -> BytesIO:
        bytes_io = BytesIO()
        cls._write_to_excel(bytes_io, headers, body_data, sheet_title, key_map)
        bytes_io.seek(0)
        return bytes_io

    @staticmethod
    def _write_to_excel(file, headers: List[HeadDict], body_data: List[Dict[str, object]], sheet_title: str = None, key_map=None):
        # excel文件的写入
        wb = Workbook()
        sheet = wb.active
        if sheet_title:
            sheet.title = sheet_title
        if not key_map:
            key_map = {
                'code': 'code',
                'name': 'name'
            }
        center_align = Alignment(horizontal='center', vertical='center')
        bold_font = Font(bold=True)
        # 书写excel标题
        for i, header in enumerate(headers, 1):
            cell = sheet.cell(row=1, column=i)
            cell.value = header[key_map['name']]
            cell.font = bold_font
            cell.alignment = center_align

            # 设置宽度, 如果有参数的话
            if header.get('excel_width'):
                # 默认13
                sheet.column_dimensions[get_column_letter(i)].width = header.get('excel_width')

            # sheet.cell(row=1, column=i, value=header[key_map['name']])

        # 填写数据
        for i, body in enumerate(body_data, 1):
            for j, header in enumerate(headers, 1):
                cell = sheet.cell(row=i + 1, column=j)
                cell.alignment = center_align
                sheet.cell(row=i + 1, column=j, value=body.get(header[key_map['code']], ''))

        wb.save(file)


if __name__ == '__main__':
    # f = open('./375T装配线参数-对应标准值.xlsx', 'rb').read()
    # print(ExcelUtils.parse_excel(f))
    test_headers = idi_full_device_exception_headers = [
        {"name": "设备名称", "code": "device_name"},
        {"name": "发生时间", "code": "create_time"},
        {"name": "操作人", "code": "operator_name"},
        {"name": "工艺参数", "code": "operate_name"},
        {"name": "下限", "code": "lower_limit"},
        {"name": "异常值", "code": "value"},
        {"name": "车间", "code": "workshop_name"},
    ]
    test_body_data = [
        {
            "_id": "6260dedcff08bdbcfb2115d6",
            "device_code": "dev_plc_sicjcx_11",
            "device_name": "成型4-11#",
            "create_time": "2022-04-21 12:34:33",
            "operator_name": "",
            "value": 120.6,
            "higher_limit": "",
            "lower_limit": 130,
            "workshop_code": "FCJ",
            "workshop_name": "四车间",
            "operate_name": "最大压制时间"
        },
        {
            "_id": "6260b4caff08bdbcfb2115d1",
            "device_code": "dev_plc_sicjcx_20",
            "device_name": "成型4-20#",
            "create_time": "2022-04-21 09:35:00",
            "operator_name": "",
            "value": 107.1,
            "higher_limit": "",
            "lower_limit": 110,
            "workshop_code": "FCJ",
            "workshop_name": "四车间",
            "operate_name": "最大压制时间"
        }
    ]
    bio = ExcelOperate.get_excel_data(test_headers, test_body_data, '最大压制时间异常')
    with open('./test.xlsx', 'wb') as f:
        f.write(bio.read())
